﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace MsDbToSql
{
    public class SQLHelper
    {
        public static string ConnString;

        public static object ExcuteScalar(string sql, SqlParameter[] paras = null, CommandType cmdType = CommandType.Text)
        {
            using (SqlConnection Conn = new SqlConnection(ConnString))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                Cmd.CommandType = cmdType;
                if (paras != null)
                {
                    Cmd.Parameters.AddRange(paras);
                }
                Conn.Open();
                return Cmd.ExecuteScalar();
            }
        }


        /// <summary>
        /// 执行insert 、update 和Delete语句
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="paras">sql语句的参数，默认为null</param>
        /// <param name="cmdType">是否是存储过程，默认不是</param>
        /// <returns></returns>
        public static int ExcuteNonQuery(string sql,SqlParameter[] paras=null,CommandType cmdType=CommandType.Text)
        {
            using (SqlConnection Conn = new SqlConnection(ConnString))
            {
                SqlCommand Cmd = new SqlCommand(sql, Conn);
                Cmd.CommandType = cmdType;
                if (paras!=null)
                {
                    Cmd.Parameters.AddRange(paras);
                }
                Conn.Open();
                return Cmd.ExecuteNonQuery();
            }
        }

        public static SqlDataReader ExcuteReader(string sql, SqlParameter[] paras = null, CommandType cmdType = CommandType.Text)
        {
             SqlConnection Conn = new SqlConnection(ConnString);
             SqlCommand Cmd = new SqlCommand(sql, Conn);
             Cmd.CommandType = cmdType;
             if (paras != null)
             {
                 Cmd.Parameters.AddRange(paras);
             }
             try
             {
                 Conn.Open();
                 SqlDataReader reader = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                 return reader;
             }
             catch(Exception ex)
             {
                 throw ex;
             }
        }

        public static DataTable ExcuteTable(string sql)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnString);
            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
            //return ExcuteDataSet(sql).Tables[0];
        }

        public static DataSet ExcuteDataSet(string sql)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnString);
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// 以事务的方式执行SQL语句
        /// </summary>
        /// <param name="sqlPrams">要执行的多条语句（sql，参数）</param>
        /// <returns>成功true，否则为false</returns>
        public static bool ExcuteWithTrans(SQLPramsPair[] sqlPrams)
        {
            string sql = ";";
            SqlConnection Conn = new SqlConnection(ConnString);
            //定义事务对象
            SqlTransaction trans = null;
            SqlCommand Cmd = new SqlCommand(sql, Conn);
            try
            {
                Conn.Open();
                trans = Conn.BeginTransaction();  //开始事务
                Cmd.Transaction = trans;

                foreach(SQLPramsPair keyPair in sqlPrams)
                {
                    Cmd.CommandText = keyPair.Sql;
                    Cmd.Parameters.AddRange(keyPair.Parms);
                    Cmd.ExecuteNonQuery();
                }
                trans.Commit();  //提交事务
                return true;
            }
            catch(Exception ex)
            {
                trans.Rollback();  //回滚事务；
                return false;
                throw ex;
            }
            finally
            {
                Conn.Close();
            }

        }

        public struct SQLPramsPair
        {
            public string Sql { get; set; }
            public SqlParameter[] Parms { get; set; }

            
            public SQLPramsPair(string sql, SqlParameter[] parms):this()
            {
                this.Sql = sql;
                this.Parms = parms;
            }
        }

    }
}
